#include "sqlitemanage.h"

//SqliteManage::SqliteManage(QObject *parent):
//    QObject(parent)
//{
//    m_db = QSqlDatabase::addDatabase("QSQLITE");
//}

SqliteManage::SqliteManage(const QString &connectionName) {
    m_connectionName = connectionName.isEmpty() ?
    QString("SQLiteConnection_%1").arg(quintptr(this)) :connectionName;

    m_db = QSqlDatabase::addDatabase("QSQLITE", m_connectionName);
}

SqliteManage::~SqliteManage()
{
    if (m_db.isOpen()) {
            m_db.close();
        }
    QSqlDatabase::removeDatabase(m_connectionName);
}

bool SqliteManage::openDatabase(const QString &dbName)
{
    m_db.setDatabaseName(dbName);
    if (!m_db.open()) {
        m_lastError = m_db.lastError().text();
        qDebug() << "Open database failed:" << m_lastError;
        return false;
    }
    return true;
}

void SqliteManage::closeDatabase()
{
    if (m_db.isOpen()) {
        m_db.close();
    }
}


//QStringList list;
//list<<"id"<<"name"<<"age";
//createTable("student",list);
bool SqliteManage::createTable(const QString &tableName, const QStringList &fields)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    if (fields.isEmpty()) {
        m_lastError = "Fields list is empty";
        return false;
    }

    QString sql = QString("CREATE TABLE IF NOT EXISTS %1 (%2)").arg(tableName).arg(fields.join(","));

    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        qDebug() << "Create table failed:" << m_lastError;
        return false;
    }

    return true;
}

//QVariantMap:QVariant类型的QMap，一次性存储一条记录的所有信息
//eg.<"id",11>,<"name","zhangsan">,<"age",12>
bool SqliteManage::insertData(const QString &tableName, const QVariantMap &data)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    if (data.isEmpty()) {
        m_lastError = "Data is empty";
        return false;
    }

    QStringList fields = data.keys();//fields:["id","name",,"age"]
    QStringList placeholders;
    for (int i = 0; i < fields.size(); ++i) {
        placeholders.append("?");//QStringList placeholders----> ["?","?","?"]
    }

    //↓↓↓↓↓↓INSERT INTO tableName (id,name,age) VALUES (?,?,?)↓↓↓↓↓↓↓↓
    QString sql = QString("INSERT INTO %1 (%2) VALUES (%3)")
                     .arg(tableName)
                     .arg(fields.join(","))
                     .arg(placeholders.join(","));//join方法:将字符串列表连接成单个字符串,使用","隔开

    QSqlQuery query(m_db);
    query.prepare(sql);

    foreach (const QString &field, fields) {
        query.addBindValue(data.value(field));
    }

    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "Insert data failed:" << m_lastError;
        return false;
    }

    return true;
}

bool SqliteManage::deleteData(const QString &tableName, const QString &condition)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    QString sql = QString("DELETE FROM %1").arg(tableName);
    if (!condition.isEmpty()) {
        sql += QString(" WHERE %1").arg(condition);
    }

    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        qDebug() << "Delete data failed:" << m_lastError;
        return false;
    }

    return true;
}

bool SqliteManage::updateData(const QString &tableName, const QVariantMap &data, const QString &condition)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    if (data.isEmpty()) {
        m_lastError = "Data is empty";
        return false;
    }

    QStringList setClauses;
    QStringList fields = data.keys();
    foreach (const QString &field, fields) {
        setClauses.append(QString("%1=?").arg(field));
    }

    QString sql = QString("UPDATE %1 SET %2").arg(tableName).arg(setClauses.join(","));
    if (!condition.isEmpty()) {
        sql += QString(" WHERE %1").arg(condition);
    }

    QSqlQuery query(m_db);
    query.prepare(sql);

    foreach (const QString &field, fields) {
        query.addBindValue(data.value(field));
    }

    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qDebug() << "Update data failed:" << m_lastError;
        return false;
    }

    return true;
}

QList<QVariantMap> SqliteManage::selectData(const QString &tableName, const QString &condition, const QStringList &fields)
{
    QList<QVariantMap> result;

    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return result;
    }

    QString fieldList = fields.isEmpty() ? "*" : fields.join(",");
    QString sql = QString("SELECT %1 FROM %2").arg(fieldList).arg(tableName);
    if (!condition.isEmpty()) {
        sql += QString(" WHERE %1").arg(condition);
    }
    qDebug() << sql;
    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        qDebug() << "Select data failed:" << m_lastError;
        return result;
    }

    while (query.next()) {
        QVariantMap record;
        for (int i = 0; i < query.record().count(); ++i) {
            record.insert(query.record().fieldName(i), query.value(i));
        }
        result.append(record);
    }

    return result;
}

bool SqliteManage::executeSql(const QString &sql)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        qDebug() << "Execute SQL failed:" << m_lastError;
        return false;
    }

    return true;
}

QString SqliteManage::lastError() const
{
    return m_lastError;
}

bool SqliteManage::tableExists(const QString &tableName)
{
    if (!m_db.isOpen()) {
        m_lastError = "Database is not open";
        return false;
    }

    QString sql = QString("SELECT name FROM sqlite_master WHERE type='table' AND name='%1'").arg(tableName);
    QSqlQuery query(m_db);
    if (!query.exec(sql)) {
        m_lastError = query.lastError().text();
        return false;
    }

    return query.next();
}
